package com.datas.service;

import com.datas.db.hibernatebeans.*;
import com.datas.service.baseentity.*;
import com.datas.utils.hbn.HbnTxRuner;
import com.datas.utils.hibernatebasebean.BaseHibernateEntity;
import org.apache.poi.ss.usermodel.*;
import org.hibernate.Session;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.util.*;

/**
 * Created by jige0727 on 2017/5/28.
 */
@Service
public class InitService {
    @Autowired
    DepartmentsService departmentsService;
    @Autowired
    UsersService usersService;
    @Autowired
    RolesService rolesService;
    @Autowired
    PageResourcesService pageResourcesService;
    @Autowired
    GuanlianService guanlianService;
    @Autowired
    RegionCountryService regionCountryService;

    //初始化总部
    public void initHQ() {
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                Integer deptid = getDeptidByDeptCode("HQ", getCurrentSession());
                if (deptid == null) {
                    UmsDepartment HQ = new UmsDepartment();
                    HQ.setDept_name("总部");
                    HQ.setDept_code("HQ");
                    HQ.setParent_dept_id(null);
                    HQ.setParent_dept_name(null);
                    departmentsService.create(HQ, getCurrentSession());
                }
                return null;
            }
        }.runTx();
    }

    //初始化 admin
    public void initAdmin() {
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                Integer userid = getUseridByUsername("admin", getCurrentSession());
                if (userid == null) {
                    UmsUser admin = new UmsUser();
                    admin.setUsername("admin");
                    admin.setPassword(UmsUser.encodePass("111111"));
                    admin.setReal_name("管理员");
                    admin.setNick_name("管理员");
                    usersService.create(admin, getCurrentSession());
                }
                return null;
            }
        }.runTx();
    }

    public void initUsers() {
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                {
                    UmsUser user = new UmsUser();
                    user.setUsername("gccgy");
                    user.setPassword(UmsUser.encodePass("111111"));
                    user.setReal_name("工厂采购员");
                    user.setNick_name("工厂采购员");
                    usersService.create(user, getCurrentSession());
                }
                return null;
            }
        }.runTx();
    }

    public void initPageResourcesFile() throws Exception {
        Workbook wb = WorkbookFactory.create(new File("D:/work2/zhongliang/菜单清单.xlsx"));
        Sheet sheet = wb.getSheetAt(0);
        JSONObject roleResources = new JSONObject();
        for (int i = 1; i < 44; i++) {
            Row row = sheet.getRow(i);
            UmsPageResource pageres = new UmsPageResource(
                    getCellValueString(row.getCell(2, Row.RETURN_BLANK_AS_NULL)),
                    getCellValueString(row.getCell(3, Row.RETURN_BLANK_AS_NULL)),
                    "1",
                    getCellValueInt(row.getCell(0, Row.RETURN_BLANK_AS_NULL)),
                    getCellValueInt(row.getCell(1, Row.RETURN_BLANK_AS_NULL)));
            pageResourcesService.create(pageres);
            roleResources.append(
                    getCellValueString(row.getCell(4, Row.RETURN_BLANK_AS_NULL)),
                    pageres.getId());
        }
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                for (String code : roleResources.keySet()) {
                    JSONArray ids = roleResources.getJSONArray(code);
                    Set<Integer> idsArr = new HashSet<>();
                    idsArr.add(1);
                    for (int i = 0; i < ids.length(); i++) {
                        idsArr.add(ids.getInt(i));

                    }
                    guanlianService.setGL_role_pageresource(
                            getRoleIdByCode(code),
                            idsArr
                    );
                }

                return 1;
            }

            private Integer getRoleIdByCode(String code) {
                return (Integer) getCurrentSession().createQuery("select i.id from UmsRole i where i.role_code=:code ")
                        .setParameter("code", code).uniqueResult();
            }
        }.runTx();
        wb.close();
    }

    private String getCellValueString(Cell cell) {
        if (cell == null) {
            return null;
        }
        return cell.getStringCellValue();
    }

    private Integer getCellValueInt(Cell cell) {
        if (cell == null) {
            return null;
        }
        return new Double(cell.getNumericCellValue()).intValue();
    }

    public void initPageResources() {
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                UmsRole role00 = new UmsRole("供应商", "00");
                UmsRole role01 = new UmsRole("供应商后台管理员", "01");
                UmsRole role02 = new UmsRole("工厂采购员", "02");
                UmsRole role03 = new UmsRole("专家", "03");
                UmsRole role04 = new UmsRole("系统管理员", "04");
                UmsRole role05 = new UmsRole("供应商管理员", "05");
                Integer insertCount = 0;
                for (UmsRole role : Arrays.asList(role00, role01, role02, role03, role04, role05)) {
                    insertCount += rolesService.create(role, getCurrentSession());
                }
                if (insertCount != 6) {
                    throw new Exception("插入角色错误:已经存在或者错误");
                }
                getCurrentSession().flush();
                ///////////////
                UmsPageResource root = new UmsPageResource("cpmp", "", "1", 1, null);
                pageResourcesService.create(root, getCurrentSession());
                UmsPageResource xtzy = new UmsPageResource("系统主页", "", "1", 1, root.getId());
                pageResourcesService.create(xtzy, getCurrentSession());
                UmsPageResource gyszy = new UmsPageResource("供应商主页", "", "1", 1, root.getId());
                pageResourcesService.create(gyszy, getCurrentSession());

                getCurrentSession().flush();
                UmsPageResource zbsq = new UmsPageResource("招标申请", "", "1", 1, root.getId());
                pageResourcesService.create(zbsq, getCurrentSession());
                getCurrentSession().flush();
                UmsPageResource tbgl = new UmsPageResource("投标管理", "", "1", 2, root.getId());
                pageResourcesService.create(tbgl, getCurrentSession());
                getCurrentSession().flush();
                UmsPageResource cpzj = new UmsPageResource("产品自荐", "", "1", 3, root.getId());
                pageResourcesService.create(cpzj, getCurrentSession());
                getCurrentSession().flush();
                UmsPageResource xxgl = new UmsPageResource("信息管理", "", "1", 4, root.getId());
                pageResourcesService.create(xxgl, getCurrentSession());
                getCurrentSession().flush();
                ///////////////////////////////////
                UmsPageResource gysgl = new UmsPageResource("供应商管理", "", "1", 5, root.getId());
                pageResourcesService.create(gysgl, getCurrentSession());
                getCurrentSession().flush();
                ///////////////////////////////////
                UmsPageResource ztbjjzxt = new UmsPageResource("招投标竞价子系统", "", "1", 6, root.getId());
                pageResourcesService.create(ztbjjzxt, getCurrentSession());
                getCurrentSession().flush();
                ///////////////////////////////////
                UmsPageResource zjpbzxt = new UmsPageResource("专家评标子系统", "", "1", 6, root.getId());
                pageResourcesService.create(zjpbzxt, getCurrentSession());
                getCurrentSession().flush();
                ///////////////////////////////////
                UmsPageResource zjgl = new UmsPageResource("专家管理", "", "1", 6, root.getId());
                pageResourcesService.create(zjgl, getCurrentSession());
                getCurrentSession().flush();
                ///////////////////////////////////
                UmsPageResource ggtblb = new UmsPageResource("公共投标列表", "/supplier_self/public_bidding_list.html", "1", 1, zbsq.getId());
                UmsPageResource yqhlb = new UmsPageResource("邀请函列表", "/supplier_self/invite_bidding_list.html", "1", 2, zbsq.getId());
                UmsPageResource wdsq = new UmsPageResource("我的申请", "/supplier_self/my_bidding_apply.html", "1", 3, zbsq.getId());
                ///////////////////////////////////
                UmsPageResource wdtblb = new UmsPageResource("我的投标列表", "/supplier_self/my_bidding_list.html", "1", 1, tbgl.getId());
                UmsPageResource yzb = new UmsPageResource("已中标", "/supplier_self/my_bidding_success.html", "1", 2, tbgl.getId());
                ///////////////////////////////////
                UmsPageResource cplb = new UmsPageResource("产品列表", "/supplier_self/product_list.html", "1", 1, cpzj.getId());
                UmsPageResource tjcp = new UmsPageResource("添加产品", "/supplier_self/add_product.html", "1", 2, cpzj.getId());
                ///////////////////////////////////
                UmsPageResource zcxxws = new UmsPageResource("注册信息完善", "/supplier_self/user_register.html", "1", 1, xxgl.getId());
                UmsPageResource yhlb = new UmsPageResource("用户列表", "/supplier_self/user_manage.html", "1", 2, xxgl.getId());
                UmsPageResource xgmm = new UmsPageResource("修改密码", "/supplier_self/change_password.html", "1", 3, xxgl.getId());
                ///////////////////////////////////
                UmsPageResource zcsh = new UmsPageResource("注册审核", "/supplier_mng/supplier_apply_list.html", "1", 1, gysgl.getId());
                UmsPageResource kfsq = new UmsPageResource("开发申请", "/supplier_mng/supplier_develop_apply_list.html", "1", 2, gysgl.getId());
                UmsPageResource gyslb = new UmsPageResource("供应商列表", "/supplier_mng/supplier_list.html", "1", 3, gysgl.getId());
                UmsPageResource ydjgys = new UmsPageResource("已冻结供应商", "/supplier_mng/supplier_black_list.html", "1", 4, gysgl.getId());
                UmsPageResource gsyhmd = new UmsPageResource("供应商黑名单", "/supplier_mng/supplier_black_list.html", "1", 5, gysgl.getId());
                UmsPageResource jxkh = new UmsPageResource("绩效考核", "/supplier_mng/examination_list.html", "1", 6, gysgl.getId());
                ///////////////////////////////////
                UmsPageResource wsjjxmlb = new UmsPageResource("网上竞价项目列表", "/bid_online/proj_list.html", "1", 1, ztbjjzxt.getId());
                UmsPageResource xxzbxmlb = new UmsPageResource("线下招标项目列表", "/bid_offline/proj_list.html", "1", 2, ztbjjzxt.getId());
                ///////////////////////////////////
                UmsPageResource pbxmlb = new UmsPageResource("评标项目列表", "/expert_grid/proj_list.html", "1", 1, zjpbzxt.getId());
                UmsPageResource cxgrxx = new UmsPageResource("查询个人信息", "/expert_grid/user_information.html", "1", 2, zjpbzxt.getId());
                UmsPageResource xgmm2 = new UmsPageResource("修改密码", "/expert_grid/change_password.html", "1", 3, zjpbzxt.getId());
                ///////////////////////////////////
                UmsPageResource zjlb = new UmsPageResource("专家列表", "/expt_mng/expt_list.html", "1", 3, zjgl.getId());
                List<UmsPageResource> resList = Arrays.asList(
                        ggtblb, yqhlb, wdsq,
                        wdtblb, yzb,
                        cplb, tjcp,
                        zcxxws, yhlb, xgmm,
                        zcsh, kfsq, gyslb, ydjgys, gsyhmd, jxkh,
                        wsjjxmlb, xxzbxmlb,
                        pbxmlb, cxgrxx, xgmm2,
                        zjlb
                );
                for (UmsPageResource item : resList) {
                    pageResourcesService.create(item, getCurrentSession());
                }
                getCurrentSession().flush();
                guanlianService.setGL_role_pageresource(role00.getId(), getids(Arrays.asList(root, zbsq, ggtblb, yqhlb, wdsq,
                        tbgl, wdtblb, yzb,
                        cpzj, cplb, tjcp,
                        xxgl, zcxxws, yhlb, xgmm)));
                guanlianService.setGL_role_pageresource(role01.getId(), getids(Arrays.asList(root, gysgl, zcsh, kfsq, gyslb, ydjgys, gsyhmd, jxkh)));
                guanlianService.setGL_role_pageresource(role02.getId(), getids(Arrays.asList(root, xtzy, ztbjjzxt, wsjjxmlb, xxzbxmlb)));//
                guanlianService.setGL_role_pageresource(role03.getId(), getids(Arrays.asList(root, zjpbzxt, pbxmlb, cxgrxx, xgmm2)));
                guanlianService.setGL_role_pageresource(role04.getId(), getids(Arrays.asList(root, zjgl, pbxmlb, zjlb,
                        gysgl, zcsh, kfsq, gyslb, ydjgys, gsyhmd, jxkh)));
                guanlianService.setGL_role_pageresource(role05.getId(), getids(Arrays.asList(root, gyszy, zbsq, ggtblb, yqhlb, wdsq,
                        tbgl, wdtblb, yzb,
                        cpzj, cplb, tjcp,
                        xxgl, zcxxws, yhlb, xgmm)));
                ///////////////////////////
                //设置角色关联
                guanlianService.setGL_user_role(getUseridByUsername("admin", getCurrentSession()), Collections.singletonList(role04.getId()));
                guanlianService.setGL_user_role(getUseridByUsername("gccgy", getCurrentSession()), Collections.singletonList(role02.getId()));
                return 1;
            }
        }.runTx();
    }

    List<Integer> getids(Collection<BaseHibernateEntity> entities) {
        List<Integer> res = new ArrayList<>();
        for (BaseHibernateEntity entity : entities) {
            res.add(entity.getId());
        }
        return res;
    }

    public void initCountry() {
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                List<UmsRegionCountry> datas = Arrays.asList(
                        new UmsRegionCountry("阿富汗", "AF", 1),
                        new UmsRegionCountry("阿尔巴尼亚", "AL", 2),
                        new UmsRegionCountry("南极洲", "AQ", 3),
                        new UmsRegionCountry("阿尔及利亚", "DZ", 4),
                        new UmsRegionCountry("美属萨摩亚", "AS", 5),
                        new UmsRegionCountry("安道尔", "AD", 6),
                        new UmsRegionCountry("安哥拉", "AO", 7),
                        new UmsRegionCountry("安提瓜和巴布达", "AG", 8),
                        new UmsRegionCountry("阿赛拜疆", "AZ", 9),
                        new UmsRegionCountry("阿根廷", "AR", 10),
                        new UmsRegionCountry("澳大利亚", "AU", 11),
                        new UmsRegionCountry("奥地利", "AT", 12),
                        new UmsRegionCountry("巴哈马", "BS", 13),
                        new UmsRegionCountry("巴林", "BH", 14),
                        new UmsRegionCountry("孟加拉国", "BD", 15),
                        new UmsRegionCountry("巴巴多斯", "BB", 16),
                        new UmsRegionCountry("比利时", "BE", 17),
                        new UmsRegionCountry("百慕大", "BM", 18),
                        new UmsRegionCountry("不丹", "BT", 19),
                        new UmsRegionCountry("玻利维亚", "BO", 20),
                        new UmsRegionCountry("波斯尼亚和墨赛哥维那", "BA", 21),
                        new UmsRegionCountry("博茨瓦纳", "BW", 22),
                        new UmsRegionCountry("布维岛", "BV", 23),
                        new UmsRegionCountry("巴西", "BR", 24),
                        new UmsRegionCountry("伯利兹", "BZ", 25),
                        new UmsRegionCountry("英属印度洋领土", "IO", 26),
                        new UmsRegionCountry("所罗门群岛", "SB", 27),
                        new UmsRegionCountry("英属维尔京群岛", "VG", 28),
                        new UmsRegionCountry("文莱", "BN", 29),
                        new UmsRegionCountry("保加利亚", "BG", 30),
                        new UmsRegionCountry("缅甸", "MM", 31),
                        new UmsRegionCountry("布隆迪", "BI", 32),
                        new UmsRegionCountry("柬埔寨", "KH", 33),
                        new UmsRegionCountry("喀麦隆", "CM", 34),
                        new UmsRegionCountry("加拿大", "CA", 35),
                        new UmsRegionCountry("佛得角", "CV", 36),
                        new UmsRegionCountry("开曼群岛", "KY", 37),
                        new UmsRegionCountry("中非", "CF", 38),
                        new UmsRegionCountry("斯里兰卡", "LK", 39),
                        new UmsRegionCountry("乍得", "TD", 40),
                        new UmsRegionCountry("智利", "CL", 41),
                        new UmsRegionCountry("中国", "CN", 42),
                        new UmsRegionCountry("圣诞岛", "CX", 43),
                        new UmsRegionCountry("哥伦比亚", "CO", 44),
                        new UmsRegionCountry("科摩罗", "KM", 45),
                        new UmsRegionCountry("马约特", "YT", 46),
                        new UmsRegionCountry("刚果", "CG", 47),
                        new UmsRegionCountry("扎伊尔", "ZR", 48),
                        new UmsRegionCountry("库克群岛", "CK", 49),
                        new UmsRegionCountry("哥斯达黎加", "CR", 50),
                        new UmsRegionCountry("克罗地亚", "HR", 51),
                        new UmsRegionCountry("古巴", "CU", 52),
                        new UmsRegionCountry("塞浦路斯", "CY", 53),
                        new UmsRegionCountry("捷克", "CZ", 54),
                        new UmsRegionCountry("贝宁", "BJ", 55),
                        new UmsRegionCountry("丹麦", "DK", 56),
                        new UmsRegionCountry("多米尼加", "DM", 57),
                        new UmsRegionCountry("多米尼加共和国", "DO", 58),
                        new UmsRegionCountry("厄瓜多尔", "EC", 59),
                        new UmsRegionCountry("萨尔瓦多", "SV", 60),
                        new UmsRegionCountry("赤道几内亚", "GQ", 61),
                        new UmsRegionCountry("埃塞俄比亚", "ET", 62),
                        new UmsRegionCountry("厄立特里亚", "ER", 63),
                        new UmsRegionCountry("爱沙尼亚", "EE", 64),
                        new UmsRegionCountry("法罗", "FO", 65),
                        new UmsRegionCountry("福克兰群岛(马尔维纳斯)", "FK", 66),
                        new UmsRegionCountry("斐济", "FJ", 67),
                        new UmsRegionCountry("芬兰", "FI", 68),
                        new UmsRegionCountry("法国", "FR", 69),
                        new UmsRegionCountry("法属圭亚那", "GF", 70),
                        new UmsRegionCountry("法属波利尼西亚", "PF", 71),
                        new UmsRegionCountry("法属南部领土", "TF", 72),
                        new UmsRegionCountry("吉布提", "DJ", 73),
                        new UmsRegionCountry("加蓬", "GA", 74),
                        new UmsRegionCountry("冈比亚", "GM", 75),
                        new UmsRegionCountry("德国", "DE", 76),
                        new UmsRegionCountry("加纳", "GH", 77),
                        new UmsRegionCountry("直布罗陀", "GI", 78),
                        new UmsRegionCountry("基里巴斯", "KI", 79),
                        new UmsRegionCountry("希腊", "GR", 80),
                        new UmsRegionCountry("格陵兰", "GL", 81),
                        new UmsRegionCountry("格林纳达", "GD", 82),
                        new UmsRegionCountry("瓜德罗普", "GP", 83),
                        new UmsRegionCountry("关岛", "GU", 84),
                        new UmsRegionCountry("危地马拉", "GT", 85),
                        new UmsRegionCountry("几内亚", "GN", 86),
                        new UmsRegionCountry("圭亚那", "GY", 87),
                        new UmsRegionCountry("海地", "HT", 88),
                        new UmsRegionCountry("赫德岛", "HM", 89),
                        new UmsRegionCountry("梵蒂冈", "VA", 90),
                        new UmsRegionCountry("洪都拉斯", "HN", 91),
                        new UmsRegionCountry("匈牙利", "HU", 92),
                        new UmsRegionCountry("冰岛", "IS", 93),
                        new UmsRegionCountry("印度", "IN", 94),
                        new UmsRegionCountry("印度尼西亚", "ID", 95),
                        new UmsRegionCountry("伊朗", "IR", 96),
                        new UmsRegionCountry("伊拉克", "IQ", 97),
                        new UmsRegionCountry("爱尔兰", "IE", 98),
                        new UmsRegionCountry("巴勒斯坦", "PS", 99),
                        new UmsRegionCountry("以色列", "IL", 100),
                        new UmsRegionCountry("意大利", "IT", 101),
                        new UmsRegionCountry("科特迪瓦", "CI", 102),
                        new UmsRegionCountry("牙买加", "JM", 103),
                        new UmsRegionCountry("日本", "JP", 104),
                        new UmsRegionCountry("哈撒克斯坦", "KZ", 105),
                        new UmsRegionCountry("约旦", "JO", 106),
                        new UmsRegionCountry("肯尼亚", "KE", 107),
                        new UmsRegionCountry("朝鲜", "KP", 108),
                        new UmsRegionCountry("南朝鲜", "KR", 109),
                        new UmsRegionCountry("科威特", "KW", 110),
                        new UmsRegionCountry("吉尔吉斯斯坦", "KG", 111),
                        new UmsRegionCountry("老挝", "LA", 112),
                        new UmsRegionCountry("莱索托", "LS", 113),
                        new UmsRegionCountry("利比里亚", "LR", 114),
                        new UmsRegionCountry("利比亚", "LY", 115),
                        new UmsRegionCountry("列支敦士登", "LI", 116),
                        new UmsRegionCountry("卢森堡", "LU", 117),
                        new UmsRegionCountry("马达加斯加", "MG", 118),
                        new UmsRegionCountry("马拉维", "MW", 119),
                        new UmsRegionCountry("马来西亚", "MY", 120),
                        new UmsRegionCountry("马尔代夫", "MV", 121),
                        new UmsRegionCountry("马里", "ML", 122),
                        new UmsRegionCountry("马耳他", "MT", 123),
                        new UmsRegionCountry("马提尼克", "MQ", 124),
                        new UmsRegionCountry("毛里塔尼亚", "MR", 125),
                        new UmsRegionCountry("毛里求斯", "MU", 126),
                        new UmsRegionCountry("墨西哥", "MX", 127),
                        new UmsRegionCountry("摩纳哥", "MC", 128),
                        new UmsRegionCountry("蒙古", "MN", 129),
                        new UmsRegionCountry("蒙特塞拉特", "MS", 130),
                        new UmsRegionCountry("摩洛哥", "MA", 131),
                        new UmsRegionCountry("莫桑比克", "MZ", 132),
                        new UmsRegionCountry("阿曼", "OM", 133),
                        new UmsRegionCountry("纳米比克", "NA", 134),
                        new UmsRegionCountry("瑙鲁", "NR", 135),
                        new UmsRegionCountry("尼泊尔", "NP", 136),
                        new UmsRegionCountry("荷兰", "NL", 137),
                        new UmsRegionCountry("荷属安的列斯", "AN", 138),
                        new UmsRegionCountry("阿鲁巴", "AW", 139),
                        new UmsRegionCountry("新喀里多怪亚", "NC", 140),
                        new UmsRegionCountry("瓦努阿图", "VU", 141),
                        new UmsRegionCountry("新西兰", "NZ", 142),
                        new UmsRegionCountry("尼加拉瓜", "NI", 143),
                        new UmsRegionCountry("尼日尔", "NE", 144),
                        new UmsRegionCountry("尼日利亚", "NG", 145),
                        new UmsRegionCountry("诺福克岛", "NF", 146),
                        new UmsRegionCountry("挪威", "NO", 147),
                        new UmsRegionCountry("北马里亚纳", "MP", 148),
                        new UmsRegionCountry("密克罗尼西亚", "FM", 149),
                        new UmsRegionCountry("马绍尔群岛", "MH", 150),
                        new UmsRegionCountry("贝劳", "PW", 151),
                        new UmsRegionCountry("巴基斯坦", "PK", 152),
                        new UmsRegionCountry("巴拿马", "PA", 153),
                        new UmsRegionCountry("巴布亚新几内亚", "PG", 154),
                        new UmsRegionCountry("巴拉圭", "PY", 155),
                        new UmsRegionCountry("秘鲁", "PE", 156),
                        new UmsRegionCountry("菲律宾", "PH", 157),
                        new UmsRegionCountry("皮特凯恩群岛", "PN", 158),
                        new UmsRegionCountry("波兰", "PL", 159),
                        new UmsRegionCountry("葡萄牙", "PT", 160),
                        new UmsRegionCountry("几内亚比绍", "GW", 161),
                        new UmsRegionCountry("东帝汶", "TL", 162),
                        new UmsRegionCountry("波多黎各", "PR", 163),
                        new UmsRegionCountry("卡塔尔", "QA", 164),
                        new UmsRegionCountry("罗马尼亚", "RO", 165),
                        new UmsRegionCountry("俄罗斯联邦", "RU", 166),
                        new UmsRegionCountry("卢旺达", "RW", 167),
                        new UmsRegionCountry("圣赫勒拿", "SH", 168),
                        new UmsRegionCountry("圣基茨和尼维斯联", "KN", 169),
                        new UmsRegionCountry("安圭拉", "AI", 170),
                        new UmsRegionCountry("圣卢西亚", "LC", 171),
                        new UmsRegionCountry("圣皮埃尔和密克隆", "PM", 172),
                        new UmsRegionCountry("圣文森特和格林纳丁斯", "VC", 173),
                        new UmsRegionCountry("圣马力诺", "SM", 174),
                        new UmsRegionCountry("圣多美和普林西比", "ST", 175),
                        new UmsRegionCountry("沙特阿拉伯", "SA", 176),
                        new UmsRegionCountry("塞内加尔", "SN", 177),
                        new UmsRegionCountry("塞舌尔", "SC", 178),
                        new UmsRegionCountry("塞拉利昂", "SL", 179),
                        new UmsRegionCountry("新加坡", "SG", 180),
                        new UmsRegionCountry("斯洛伐克", "SK", 181),
                        new UmsRegionCountry("越南", "VN", 182),
                        new UmsRegionCountry("索马里", "SO", 183),
                        new UmsRegionCountry("南非", "ZA", 184),
                        new UmsRegionCountry("津巴布韦", "ZW", 185),
                        new UmsRegionCountry("西班牙", "ES", 186),
                        new UmsRegionCountry("西撒哈拉", "EH", 187),
                        new UmsRegionCountry("苏丹", "SD", 188),
                        new UmsRegionCountry("苏里南", "SR", 189),
                        new UmsRegionCountry("斯威士兰", "SZ", 190),
                        new UmsRegionCountry("瑞典", "SE", 191),
                        new UmsRegionCountry("瑞士", "CH", 192),
                        new UmsRegionCountry("叙利亚", "SY", 193),
                        new UmsRegionCountry("塔吉克斯坦", "TJ", 194),
                        new UmsRegionCountry("泰国", "TH", 195),
                        new UmsRegionCountry("多哥", "TG", 196),
                        new UmsRegionCountry("汤加", "TO", 197),
                        new UmsRegionCountry("特立尼达和多巴哥", "TT", 198),
                        new UmsRegionCountry("阿拉伯联合酋长国", "AE", 199),
                        new UmsRegionCountry("突尼斯", "TN", 200),
                        new UmsRegionCountry("土耳其", "TR", 201),
                        new UmsRegionCountry("土库曼斯坦", "TM", 202),
                        new UmsRegionCountry("特克斯和凯科斯群岛", "TC", 203),
                        new UmsRegionCountry("图瓦卢", "TV", 204),
                        new UmsRegionCountry("乌干达", "UG", 205),
                        new UmsRegionCountry("乌克兰", "UA", 206),
                        new UmsRegionCountry("马其顿共和国", "MK", 207),
                        new UmsRegionCountry("埃及", "EG", 208),
                        new UmsRegionCountry("英国", "GB", 209),
                        new UmsRegionCountry("坦桑尼亚", "TZ", 210),
                        new UmsRegionCountry("美国", "US", 211),
                        new UmsRegionCountry("美属维尔京群岛", "VI", 212),
                        new UmsRegionCountry("布基纳法索", "BF", 213),
                        new UmsRegionCountry("乌拉圭", "UY", 214),
                        new UmsRegionCountry("乌兹别克斯坦", "UZ", 215),
                        new UmsRegionCountry("委内瑞拉", "VE", 216),
                        new UmsRegionCountry("瓦利斯和富图纳群岛", "WF", 217),
                        new UmsRegionCountry("西萨摩亚", "WS", 218),
                        new UmsRegionCountry("也门民主人民共和国", "YE", 219),
                        new UmsRegionCountry("南斯拉夫", "YU", 220),
                        new UmsRegionCountry("赞比亚", "ZM", 221),
                        new UmsRegionCountry("俄罗斯", "RU", 222),
                        new UmsRegionCountry("白俄罗斯", "BY", 223),
                        new UmsRegionCountry("立陶宛", "LT", 224),
                        new UmsRegionCountry("拉脱维亚", "LV", 225),
                        new UmsRegionCountry("哈萨克斯坦", "KZ", 226),
                        new UmsRegionCountry("格鲁吉亚", "GE", 227),
                        new UmsRegionCountry("亚美尼亚", "AM", 228),
                        new UmsRegionCountry("摩尔多瓦", "MD", 229),
                        new UmsRegionCountry("台湾省", "TW", 230),
                        new UmsRegionCountry("香港特别行政区", "HK", 231),
                        new UmsRegionCountry("澳门特别行政区", "MO", 232)
                );
                for (UmsRegionCountry data : datas) {
                    regionCountryService.create(data, getCurrentSession());
                }
                return 1;
            }
        }.runTx();
    }

    private Integer getUseridByUsername(String username, Session session) {
        return (Integer) session.createQuery("select i.id from UmsUser i where i.username=:username ")
                .setParameter("username", username)
                .uniqueResult();
    }

    private Integer getDeptidByDeptCode(String dept_code, Session session) {
        return (Integer) session.createQuery("select i.id from UmsDepartment i where i.dept_code=:dept_code ")
                .setParameter("dept_code", dept_code)
                .uniqueResult();
    }

    public void initDepartments() throws Exception {
        Workbook wb = WorkbookFactory.create(new File("D:/work2/zhongliang/SAP组织机构信息_20170612_v1.0.xlsx"));
        new HbnTxRuner() {
            @Override
            protected Object dataLogic() throws Exception {
                initCorp(wb, getCurrentSession());
                return 1;
            }
        }.runTx();
        wb.close();
    }

    private Boolean isExist(String code, Session session) {
        Long count = (Long) session
                .createQuery("select count(*) from UmsDepartment i where i.dept_code=:dept_code ")
                .setParameter("dept_code", code)
                .uniqueResult();
        return count > 0;
    }

    private void initCorp(Workbook wb, Session session) throws Exception {
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i < 108; i++) {
            Row row = sheet.getRow(i);
            String code = row.getCell(0).getStringCellValue();
            String name = row.getCell(1).getStringCellValue();
            if (!isExist(code, session)) {
                UmsDepartment dept = new UmsDepartment();
                dept.setDept_name(name);
                dept.setDept_code(code);
                dept.setParent_dept_id(getDeptidByDeptCode("HQ", session));
                departmentsService.create(dept, session);
            }
            session.flush();
            String subcode = row.getCell(2).getStringCellValue();
            String subname = row.getCell(3).getStringCellValue();
            if (!isExist(subcode, session)) {
                UmsDepartment dept = new UmsDepartment();
                dept.setDept_name(subname);
                dept.setDept_code(subcode);
                dept.setParent_dept_id(getDeptidByDeptCode(code, session));
                departmentsService.create(dept, session);
            }
            session.flush();
        }
        sheet = wb.getSheetAt(1);
        for (int i = 1; i < 837; i++) {
            Row row = sheet.getRow(i);
            String code = row.getCell(0).getStringCellValue();
            String name = row.getCell(1).getStringCellValue();
            String subcode = row.getCell(2).getStringCellValue();
            String subname = row.getCell(3).getStringCellValue();
            if (!isExist(subcode, session)) {
                UmsDepartment dept = new UmsDepartment();
                dept.setDept_name(subname);
                dept.setDept_code(subcode);
                dept.setParent_dept_id(getDeptidByDeptCode(code, session));
                departmentsService.create(dept, session);
            }
            session.flush();
        }
    }
}
